﻿'========================================================================
'功能:群組物件資料庫存取物件
'作者:andy
'日期: 2011/1/28
'========================================================================

Imports Microsoft.VisualBasic
Imports System
Imports System.Collections.Generic
Imports System.Data.SqlClient
Imports System.Text
Imports System.Data
Namespace OAMS.DAO
    Public Class GroupDAO
        Inherits BaseDAO
        Dim _dbName As String
        Public Sub New(ByVal dbName As String)
            _dbName = dbName
        End Sub

        ''' <summary>
        ''' 取得所有群組資料
        ''' </summary>
        ''' <returns>取得所有群組資料</returns>
        ''' <remarks>取得所有群組資料</remarks>
        Public Function GetGroupIDdt() As DataTable
            Dim sqlParams As SqlParameter() = New SqlParameter(1) {}


            Dim sb As New StringBuilder()
            sb.AppendLine("Select * from GroupID ")
            Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString()).Tables(0)


        End Function

        ''' <summary>
        ''' 取得所有群組資料及一筆可以新增的紀錄
        ''' </summary>
        ''' <returns>取得所有群組資料及一筆可以新增的紀錄</returns>
        ''' <remarks>取得所有群組資料及一筆可以新增的紀錄</remarks>
        Public Function GetGroupIDInsert() As DataTable
            Dim sqlParams As SqlParameter() = New SqlParameter(1) {}


            Dim sb As New StringBuilder()
            sb.AppendLine("Select GroupID,GroupName,RootID, 0 as canModi from GroupID union select  '' as GroupID,'' as GroupName ,'' as RootID,1 as canModi order by GroupID")
            Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString()).Tables(0)


        End Function


        ''' <summary>
        ''' 取得群組權限的資料
        ''' </summary>
        ''' <param name="GroupID"></param>
        ''' <param name="ProgramID"></param>
        ''' <returns>取得群組權限的資料</returns>
        ''' <remarks>取得群組權限的資料</remarks>
        Public Function GetGroupRights(ByVal GroupID As String, ByVal ProgramID As Integer) As DataTable
            Dim sqlParams As SqlParameter() = New SqlParameter(2) {}

            sqlParams(0) = New SqlParameter("@ProgramID", SqlDbType.Int)
            sqlParams(0).Value = ProgramID
            sqlParams(1) = New SqlParameter("@GroupID", SqlDbType.NVarChar, 100)
            sqlParams(1).Value = GroupID
            Dim sb As New StringBuilder()
            sb.AppendLine("create table  #AA")
            sb.AppendLine("( SeqNo int,DisplayName nvarchar(100),UseRight bit)")
            sb.AppendLine("insert into #AA")
            sb.AppendLine("select A.SeqNo,A.DisplayName ,B.UseRight from MenuRights  A")
            sb.AppendLine("left join  GroupRights   B on A.ProgramID =B.ProgramID and A.SeqNo=B.SeqNo ")
            sb.AppendLine("where B.GroupID =@GroupID and A.ProgramID =@ProgramID")
            sb.AppendLine("insert into #AA")
            sb.AppendLine("select A.SeqNo,A.DisplayName ,0 as UseRight from MenuRights  A")
            sb.AppendLine("where A.ProgramID =@ProgramID  and (not SeqNo  in (Select SeqNo from #AA ) )")
            sb.AppendLine("select * from #AA   order by SeqNo drop table #AA")
            Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).Tables(0)


        End Function

        ''' <summary>
        ''' 修改群組權限的資料
        ''' </summary>
        ''' <param name="GroupID"></param>
        ''' <param name="ProgramID"></param>
        ''' <param name="SeqNo"></param>
        ''' <param name="UseRight"></param>
        ''' <param name="UID"></param>
        ''' <remarks>修改群組權限的資料</remarks>
        Public Sub UpdateRight(ByVal GroupID As String, ByVal ProgramID As Integer, ByVal SeqNo As Integer, ByVal UseRight As Boolean, ByVal UID As String)
            Dim sqlParams As SqlParameter() = New SqlParameter(4) {}

            sqlParams(0) = New SqlParameter("@ProgramID", SqlDbType.Int)
            sqlParams(0).Value = ProgramID
            sqlParams(1) = New SqlParameter("@GroupID", SqlDbType.NVarChar, 100)
            sqlParams(1).Value = GroupID
            sqlParams(2) = New SqlParameter("@SeqNo", SqlDbType.Int)
            sqlParams(2).Value = SeqNo
            sqlParams(3) = New SqlParameter("@UID", SqlDbType.NVarChar, 100)
            sqlParams(3).Value = UID
            Dim sb As New StringBuilder()
            If UseRight Then
                sb.AppendLine("delete from GroupRights where ProgramID=@ProgramID and SeqNo=@SeqNo and GroupID=@GroupID")
                sb.AppendLine("insert into GroupRights (ProgramID,GroupID,SeqNo,UseRight,CrtUID,CrtDate,ModiUID,ModiDate) values (@ProgramID,@GroupID,@SeqNo,1,@UID,getdate(),'',null)")
            Else
                sb.AppendLine("delete from GroupRights where ProgramID=@ProgramID and SeqNo=@SeqNo and GroupID=@GroupID and( GroupID=@UID or 1=1)")
            End If

            SqlHelper.ExecuteNonQuery(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams)

        End Sub

        ''' <summary>
        ''' 修改或新增一筆資料到群組資料表中
        ''' </summary>
        ''' <param name="GroupID"></param>
        ''' <param name="GroupName"></param>
        ''' <param name="UID"></param>
        ''' <param name="canModi">true代表是新增</param>
        ''' <remarks>修改或新增一筆資料到群組資料表中</remarks>
        Public Sub UpdateGroupID(ByVal GroupID As String, ByVal GroupName As String, ByVal UID As String, ByVal canModi As Boolean, ByVal RootID As String)
            Dim sqlParams As SqlParameter() = New SqlParameter(4) {}


            sqlParams(0) = New SqlParameter("@GroupID", SqlDbType.NVarChar, 100)
            sqlParams(0).Value = GroupID
            sqlParams(1) = New SqlParameter("@GroupName", SqlDbType.NVarChar, 100)
            sqlParams(1).Value = GroupName
            sqlParams(2) = New SqlParameter("@UID", SqlDbType.NVarChar, 100)
            sqlParams(2).Value = UID
            sqlParams(3) = New SqlParameter("@RootID", SqlDbType.NVarChar, 100)
            sqlParams(3).Value = RootID
            Dim sb As New StringBuilder()
            If canModi Then
                sb.AppendLine("insert into GroupID (GroupID,GroupName,CrtUID,CrtDate,ModiUID,ModiDate,RootID)values (@GroupID,@GroupName,@UID,GetDate(),'',null,@RootID )")
            Else
                sb.AppendLine("update GroupID set GroupName=@GroupName ,ModiUID=@UID ,ModiDate=getDate(),RootID=@RootID where GroupID=@GroupID")
            End If
            SqlHelper.ExecuteNonQuery(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams)

        End Sub


        ''' <summary>
        ''' 檢查某一個群組代號是否存在
        ''' </summary>
        ''' <param name="GroupID"></param>
        ''' <returns>檢查某一個群組代號是否存在</returns>
        ''' <remarks>檢查某一個群組代號是否存在</remarks>
        Public Function GroupIDIsExist(ByVal GroupID As String) As Boolean
            Dim sqlParams As SqlParameter() = New SqlParameter(1) {}
            sqlParams(0) = New SqlParameter("@GroupID", SqlDbType.NVarChar, 100)
            sqlParams(0).Value = GroupID

            Dim sb As New StringBuilder()
            sb.AppendLine("Select * from GroupID where GroupID=@GroupID")
            Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).Tables(0).Rows.Count > 0
        End Function
        ''' <summary>
        ''' 檢查人員是否屬於某一群組
        ''' </summary>
        ''' <param name="GroupID"></param>
        ''' <param name="UID"></param>
        ''' <returns>檢查人員是否屬於某一群組</returns>
        ''' <remarks>檢查人員是否屬於某一群組</remarks>
        Public Function GroupMemberIsExist(ByVal GroupID As String, ByVal UID As String) As Boolean
            Dim sqlParams As SqlParameter() = New SqlParameter(2) {}
            sqlParams(0) = New SqlParameter("@GroupID", SqlDbType.NVarChar, 100)
            sqlParams(0).Value = GroupID
            sqlParams(1) = New SqlParameter("@UID", SqlDbType.NVarChar, 100)
            sqlParams(1).Value = UID

            Dim sb As New StringBuilder()
            sb.AppendLine("Select * from GroupMember where GroupID=@GroupID and UID=@UID ")
            Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).Tables(0).Rows.Count > 0
        End Function

        ''' <summary>
        ''' 新增群組人員資料
        ''' </summary>
        ''' <param name="GroupID"></param>
        ''' <param name="UID1"></param>
        ''' <param name="CrtUID"></param>
        ''' <remarks>新增群組人員資料</remarks>
        Public Sub InsertGroupMember(ByVal GroupID As String, ByVal UID1 As String, ByVal CrtUID As String)
            Dim sqlParams As SqlParameter() = New SqlParameter(3) {}
            sqlParams(0) = New SqlParameter("@GroupID", SqlDbType.NVarChar, 100)
            sqlParams(0).Value = GroupID
            sqlParams(1) = New SqlParameter("@UID", SqlDbType.NVarChar, 100)
            sqlParams(1).Value = UID1
            sqlParams(2) = New SqlParameter("@CrtUID", SqlDbType.NVarChar, 100)
            sqlParams(2).Value = CrtUID

            Dim sb As New StringBuilder()
            sb.AppendLine(" insert into   GroupMember (GroupID,UID,CrtUID,CrtDate,ModiUID,ModiDate) values(@GroupID ,@UID,@CrtUID,getDate(),'',null) ")
            SqlHelper.ExecuteNonQuery(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams)
        End Sub
        ''' <summary>
        ''' 刪除群組人員資料
        ''' </summary>
        ''' <param name="GroupID"></param>
        ''' <param name="UID"></param>
        ''' <remarks>刪除群組人員資料</remarks>
        Public Sub DeleteGroupMember(ByVal GroupID As String, ByVal UID As String)
            Dim sqlParams As SqlParameter() = New SqlParameter(2) {}
            sqlParams(0) = New SqlParameter("@GroupID", SqlDbType.NVarChar, 100)
            sqlParams(0).Value = GroupID
            sqlParams(1) = New SqlParameter("@UID", SqlDbType.NVarChar, 100)
            sqlParams(1).Value = UID

            Dim sb As New StringBuilder()
            sb.AppendLine(" delete from  GroupMember  where GroupID=@GroupID and UID=@UID")
            SqlHelper.ExecuteNonQuery(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams)
        End Sub

        ''' <summary>
        ''' 取得個人的所有群組資料
        ''' </summary>
        ''' <param name="UID"></param>
        ''' <returns>取得個人的所有群組資料</returns>
        ''' <remarks>取得個人的所有群組資料</remarks>
        Public Function GetGroupIDByUID(ByVal UID As String) As DataTable
            Dim sqlParams As SqlParameter() = New SqlParameter(1) {}

            sqlParams(0) = New SqlParameter("@UID", SqlDbType.NVarChar, 100)
            sqlParams(0).Value = UID
            Dim sb As New StringBuilder()
            sb.AppendLine("select A.GroupID,B.GroupName ,0 as canModi,1 as canDelete  from GroupMember A left join GroupID B on A.GroupID=B.GroupID where A.UID=@UID  union Select '' as GroupID,'' as GroupName,1 as canModi,0 as canDelete")
            Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).Tables(0)


        End Function
        ''' <summary>
        ''' 
        ''' </summary>
        ''' <param name="UID"></param>
        ''' <param name="Kind">1加1筆空白,0不加</param>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Function GetGroupIDByUID(ByVal UID As String, ByVal Kind As String) As DataTable
            Dim sqlParams As SqlParameter() = New SqlParameter(1) {}

            sqlParams(0) = New SqlParameter("@UID", SqlDbType.NVarChar, 100)
            sqlParams(0).Value = UID
            Dim sb As New StringBuilder()
            If Kind = "1" Then
                sb.AppendLine("select A.GroupID,B.GroupName ,0 as canModi,1 as canDelete  from GroupMember A left join GroupID B on A.GroupID=B.GroupID where A.UID=@UID  union Select '' as GroupID,'' as GroupName,1 as canModi,0 as canDelete")
            Else
                sb.AppendLine("select A.GroupID,B.GroupName ,0 as canModi,1 as canDelete  from GroupMember A left join GroupID B on A.GroupID=B.GroupID where A.UID=@UID ")
            End If
            Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).Tables(0)


        End Function


        ''' <summary>
        ''' 取得群組的所有人員資料
        ''' </summary>
        ''' <param name="GroupID"></param>
        ''' <returns>取得群組的所有人員資料</returns>
        ''' <remarks>取得群組的所有人員資料</remarks>
        Public Function GetUIDByGroupID(ByVal GroupID As String) As DataTable
            Dim sqlParams As SqlParameter() = New SqlParameter(1) {}

            sqlParams(0) = New SqlParameter("@GroupID", SqlDbType.NVarChar, 100)
            sqlParams(0).Value = GroupID
            Dim sb As New StringBuilder()
            sb.AppendLine("select B.emp_name as UserName, A.UID as UID1 ,0 as canModi, 1 as canDelete from GroupMember A left join Users B on A.UID =B.emp_no    where GroupID=@GroupID  union select '' as UserName, '' as UID1 ,1 as canModi ,0 as canDelete order by UID ")
            Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).Tables(0)


        End Function
    End Class

End Namespace
